package usi.ch.onto.yago;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import usi.ch.onto.common.StringUtils;

public class MatchingStrings {
	private final String INDEXES = "../yagoaida_resources/bigindex";
	private final String CONNECTION_STRING = "jdbc:mysql://localhost/yago";
	private final int BATCH_MAX = 30000;
	private final String USER = "root";
	private final String PASS = "admin";
	private final String RESULTS_TABLE_SUFIX = "results";

	private List<String> listTerms;
	private Connection conn;

	public enum TypeOfMatching {
		arg1, arg1or2, arg1and2, arg2, means, subclass
	}

	public void setListTerms(List<String> listTerms) {
		this.listTerms = listTerms;
	}

	public List<String> getListTerms() {
		return listTerms;
	}

	public MatchingStrings(List<String> listTermsToMatch) throws Exception {
		this.setListTerms(listTermsToMatch);
		conn = DriverManager.getConnection(CONNECTION_STRING, USER, PASS);
		conn.setAutoCommit(false);
		if (conn.isClosed())
			throw new Exception("Error connection to db ");
	}

	private String getResultsTableName(String originTableName) {
		return originTableName + RESULTS_TABLE_SUFIX;
	}

	// results will be placed in tableOrigin + "Results"
	public List<String> matchTerms(String tableOrigin,
			TypeOfMatching typematching) throws Exception {

		List<String> noMatchList = null;
		switch (typematching) {
		case arg1:
			noMatchList = matchArgx(tableOrigin, getListTerms(), TypeOfMatching.arg1);
			break;
		case arg1and2:
			// TODO
			// noMatchList = matchArgXandY(tableOrigin, listTerms);

			break;
		case arg1or2:
			noMatchList = matchArgXorY(tableOrigin, getListTerms());

			break;
		case arg2:
			noMatchList = matchArgx(tableOrigin, getListTerms(), TypeOfMatching.arg2);
			break;
		case means:
			noMatchList = getMeansArg2(tableOrigin, getListTerms());
			break;
		case subclass:
			noMatchList = matchArgx(tableOrigin, getListTerms(), TypeOfMatching.arg1);
		default:
			//nothing
			break;
		}

		return noMatchList;
	}

	
	public void subclassPhase() throws Exception{
		//search subclass table
		conn.setAutoCommit(true);
		PreparedStatement pstmtInsert = conn.prepareStatement("insert into subclassresults (tabid, matchstring)	select sub.id as tabid, sub.arg1 as matchstring from subclass sub, tabtypes typ	where typ.name = sub.arg1");
		
		System.out.println("check types in subclass table");
		pstmtInsert.executeUpdate();
		System.out.println("done");
	}
	
	public void phase2() throws Exception{
		fillEntitiesTable();
		fillTypesTable();
	}
	
	//insert arg1 from tab1Results
	//insert arg2 from tab2Results
	//insert arg1 from tab1or2Results where whichArg = 1
	//insert arg2 from tab1or2Results where whichArg = 2
	public void fillEntitiesTable() throws Exception{
		conn.setAutoCommit(true);
		PreparedStatement pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntities (name) select tab.arg1 as name from tab1Results res , tab1 tab where tab.id = res.tabid");
		System.out.println("insert arg1 from tab1Results");
		pstmtInsert.executeUpdate();
		
		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntities (name) select tab.arg2 as name from tab2Results res , tab1 tab where tab.id = res.tabid");
		System.out.println("insert arg2 from tab2Results");
		pstmtInsert.executeUpdate();

		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntities (name) select tab.arg1 as name from tab1or2Results res , tab1or2 tab where tab.id = res.tabid and res.whichArg = 1");
		System.out.println("insert arg1 from tab1or2Results where whichArg = 1");
		pstmtInsert.executeUpdate();

		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntities (name) select tab.arg2 as name from tab1or2Results res , tab1or2 tab where tab.id = res.tabid and res.whichArg = 2");
		System.out.println("insert arg2 from tab1or2Results where whichArg = 2");
		pstmtInsert.executeUpdate();
		
		
		//for subclass.arg1 in rows of subclassresults,  match subclass.arg1 with tabext.arg2, 
		//store arg1, arg2 from tabext in tabEntities and tabTypes
		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntities (name) select ext.arg1 as name from subclassResults res, subclass tab, tabext ext where tab.id = res.tabid and tab.arg1 = ext.arg2");
		System.out.print("insert arg1 from subclassResults where subclass.Arg1 = tabext.Arg2...");
		pstmtInsert.executeUpdate();		
		System.out.println("done");
		
		System.out.println("");		
		System.out.println("done fillEntitiesTable");
		conn.setAutoCommit(false);
	}
	
	//match entities from tabEntities 
	//
	public void fillTypesTable() throws Exception{
		//insert records in tabExtResults
		conn.setAutoCommit(true);
		PreparedStatement pstmtInsert = 
			conn.prepareStatement("insert ignore into tabextresults (tabid, matchstring) select tab.id as tabid, tab.arg1 as matchstring from tabext tab where exists (select entity.name from tabEntities entity where entity.name = tab.arg1 limit 1)");
		System.out.println("insert into tabextresults");
		pstmtInsert.executeUpdate();
		
		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabTypes (name) select tab.arg2 as name from tabext tab, tabExtResults res where res.tabid = tab.id");
		System.out.println("insert into tabTypes from tabExtResults");
		pstmtInsert.executeUpdate();

		
		//Not necessary because we already inserted in tabtypes , all the types that came from tabextresults, 
		//and those came from the entities table which were inserted before
//		pstmtInsert = conn.prepareStatement("insert ignore into tabTypes (name) select ext.arg2 as name from subclassResults res, subclass tab, tabext ext where tab.id = res.tabid and tab.arg1 = ext.arg2");
//		pstmtInsert.executeUpdate();		
		
		pstmtInsert = 
			conn.prepareStatement("insert ignore into tabEntitiesTypes (tabEntitiesId, tabTypesId) select entity.id as tabEntitiesId, typ.id as tabTypesId  from tabEntities entity , tabTypes typ , tabExtResults res , tabExt tab where entity.name = res.matchString and res.tabid = tab.id and tab.arg2 = typ.name");
		System.out.print("insert into tabEntitiesTypes...");
		pstmtInsert.executeUpdate();
		System.out.println("done");
		
		pstmtInsert = 
			conn.prepareStatement("update tabTypes typ set typ.count = (select count(*) from tabEntitiesTypes et where et.tabTypesId = typ.id)");
		System.out.print("update counter in tabTypes...");
		pstmtInsert.executeUpdate();		
		System.out.println("done");
		
		System.out.println("");
		System.out.println("done fillTypesTable");
		
		conn.setAutoCommit(false);
	}
	
	private List<String> getMeansArg2(String tableOrigin, List<String> listTerms)
			throws Exception {
		PreparedStatement pstmtMatchSingleTerm = conn
				.prepareStatement(getSelectTableMatchSingleTermArg1GetArg2SQL(tableOrigin));

		List<PreparedStatement> matchMultipleTermStatementList = getPreparedStatementMatchMultipleTermListArg1GetTerm2(tableOrigin);

		Set<String> listArg2ThatMatchedArg1 = getListArg2thatMatchArg1(
				listTerms, pstmtMatchSingleTerm, matchMultipleTermStatementList);
		List<String> listItems = new ArrayList<String>();
		for (String arg2 : listArg2ThatMatchedArg1)
			listItems.add(arg2);

		return listItems;
	}

	private Set<String> getListArg2thatMatchArg1(List<String> listTerms,
			PreparedStatement pstmtMatchSingleTerm,
			List<PreparedStatement> matchMultipleTermStatementList)
			throws Exception {
		Set<String> listOfMatchedArg2 = new HashSet<String>();

		List<String> arrayMatchTerms;
		ResultSet matchesResult;
		int numTerms;
		PreparedStatement stmtMultiple;
		for (String string2match : listTerms) {
			arrayMatchTerms = StringUtils.fastSplit(string2match, SEPARATOR_UNDERSCORE, SEPARATOR_WHITE_SPACE);
			numTerms = arrayMatchTerms.size();
			if (numTerms > 1) {
				stmtMultiple = getPreparedStatementForMultipleTerm(
						matchMultipleTermStatementList, numTerms);
				for (int i = 0; i < numTerms; i++) {
					stmtMultiple.setString(i + 1, arrayMatchTerms.get(i));
				}
				matchesResult = stmtMultiple.executeQuery();

			} else {
				// single term
				pstmtMatchSingleTerm.setString(1, string2match);
				matchesResult = pstmtMatchSingleTerm.executeQuery();
			}

			if (matchesResult.next() == false) {
				//if no result we add the original term 
				//to be spitted out as no match
				listOfMatchedArg2.add(string2match);
			} else {
				matchesResult.previous();
				while (matchesResult.next()) {
					listOfMatchedArg2.add(matchesResult.getString(1));
				}
			}
		}

		return listOfMatchedArg2;
	}

	// the process here is to match in arg1
	// get the arg2 if exists in the lists of terms then its a match
	// private void matchArgXandY(String tableOrigin, List<String> listTerms)
	// throws Exception {
	//
	// List<String> noMatchList = new ArrayList<String>();
	//
	// PreparedStatement pstmtMatchSingleTerm = conn
	// .prepareStatement(getSelectTableMatchSingleTermArg1AndArg2SQL(tableOrigin));
	//
	// List<PreparedStatement> matchMultipleTermStatementList =
	// getPreparedStatementMatchMultipleTermListArg1AndArg2(tableOrigin);
	//
	// PreparedStatement pstmtInsertResults = conn
	// .prepareStatement(getInsertResultsSQL(getResultsTableName(tableOrigin)));
	//
	// processListTermsWithStatements(listTerms, pstmtMatchSingleTerm,
	// matchMultipleTermStatementList, pstmtInsertResults, noMatchList,
	// typeOfMatching.arg1and2);
	// }

	private List<String> matchArgXorY(String tableOrigin, List<String> listTerms)
			throws Exception {

		List<String> noMatchList = new ArrayList<String>();

		PreparedStatement pstmtMatchSingleTerm = conn
				.prepareStatement(getSelectTableMatchSingleTermArg1OrArg2SQL(tableOrigin));

		List<PreparedStatement> matchMultipleTermStatementList = getPreparedStatementMatchMultipleTermListArg1OrArg2(tableOrigin);

		PreparedStatement pstmtInsertResults = conn
				.prepareStatement(getInsertResultsSQL(getResultsTableName(tableOrigin)));

		processListTermsWithStatements(listTerms, pstmtMatchSingleTerm,
				matchMultipleTermStatementList, pstmtInsertResults,
				noMatchList, TypeOfMatching.arg1or2);

		return noMatchList;
	}

	// we try to match only in argument1 column
	private List<String> matchArgx(String tableOrigin, List<String> listTerms,
			TypeOfMatching whichArg) throws Exception {

		List<String> noMatchList = new ArrayList<String>();

		PreparedStatement pstmtMatchSingleTerm = conn
				.prepareStatement(getSelectTableMatchSingleTermSQL(tableOrigin,
						whichArg));

		List<PreparedStatement> matchMultipleTermStatementList = getPreparedStatementMatchMultipleTermList(
				tableOrigin, whichArg);

		PreparedStatement pstmtInsertResults = conn
				.prepareStatement(getInsertResultsSQL(getResultsTableName(tableOrigin)));

		processListTermsWithStatements(listTerms, pstmtMatchSingleTerm,
				matchMultipleTermStatementList, pstmtInsertResults,
				noMatchList, whichArg);

		return noMatchList;
	}

	private void processListTermsWithStatements(List<String> listTerms,
			PreparedStatement pstmtMatchSingleTerm,
			List<PreparedStatement> matchMultipleTermStatementList,
			PreparedStatement pstmtInsertResults, List<String> noMatchList,
			TypeOfMatching whichArg

	) throws Exception {
		List<String> arrayMatchTerms;
		ResultSet matchesResult;
		int numTerms;
		PreparedStatement stmtMultiple;
		int batchCount = 0, insertCount = 0;
		for (String string2match : listTerms) {
			arrayMatchTerms = StringUtils.fastSplit(string2match, SEPARATOR_UNDERSCORE, SEPARATOR_WHITE_SPACE);
			numTerms = arrayMatchTerms.size();
			if (numTerms > 1) {
				stmtMultiple = getPreparedStatementForMultipleTerm(
						matchMultipleTermStatementList, numTerms);
				for (int i = 0; i < numTerms; i++) {
					stmtMultiple.setString(i + 1, arrayMatchTerms.get(i));
				}
				if (whichArg == TypeOfMatching.arg1or2
						|| whichArg == TypeOfMatching.arg1and2) {
					for (int i = 0, j = numTerms; i < numTerms; i++, j++) {
						stmtMultiple.setString(j + 1, arrayMatchTerms.get(i));
					}
				}
				matchesResult = stmtMultiple.executeQuery();

			} else {
				// single term
				pstmtMatchSingleTerm.setString(1, string2match);
				if (whichArg == TypeOfMatching.arg1or2
						|| whichArg == TypeOfMatching.arg1and2) {
					// in this case we have one more param in the query with the
					// same term
					pstmtMatchSingleTerm.setString(2, string2match);
				}
				matchesResult = pstmtMatchSingleTerm.executeQuery();
			}
			insertCount = addBatchInsertResults(pstmtInsertResults,
					matchesResult, string2match, whichArg);
			if (insertCount == 0)
				noMatchList.add(string2match);
			else {
				batchCount += insertCount;
				if (batchCount >= BATCH_MAX) {
					batchCount = 0;
					commitStatement(pstmtInsertResults);
				}
			}
		}
		if (batchCount > 0)
			commitStatement(pstmtInsertResults);
	}

	// argType should be ONLY arg1 OR arg2
	private String getArgsTableName(String table, TypeOfMatching whichArg) {
		String tableArgsName = table;
		switch (whichArg) {
		case arg1:
			tableArgsName += "Arg1Terms";
			break;
		case arg2:
			tableArgsName += "Arg2Terms";
			break;
		default:
			// throw new Exception("wrong arg type!");
			break;
		}
		return tableArgsName;
	}

	@SuppressWarnings("unused")
	private String getSelectTableMatchSingleTermArg1AndArg2SQL(String tablename) {
		// TODO

		return "";
	}

	private String getSelectTableMatchMultipleTermArg1AndArg2SQL(
			String tablename, int numTerms) {
		// TODO

		return "";
	}

	private String getSelectTableMatchSingleTermArg1GetArg2SQL(String tablename) {
		String tableArg1 = getArgsTableName(tablename, TypeOfMatching.arg1);

		StringBuilder sb = new StringBuilder();
		sb.append("(select tab.Arg2 from ");
		sb.append(tablename);
		sb.append(" tab, ");
		sb.append(tableArg1);
		sb.append(" aliasArg1 ");
		sb.append(" where tab.id = aliasArg1.tabid ");
		sb.append(" and tab.isArg1SingleTerm = true ");
		sb.append(" and tab.factName = 'means' ");
		sb.append(" and aliasArg1.term = ? )");

		return sb.toString();
	}

	private String getSelectTableMatchMultipleTermArg1GetArg2SQL(
			String tablename, int numTerms) {
		String tableArg1 = getArgsTableName(tablename, TypeOfMatching.arg1);
		StringBuilder sb = new StringBuilder();
		sb.append("select distinct tab.Arg2 from ");
		sb.append(tablename + " tab, ");
		sb.append(tableArg1 + " alias1");
		for (int i = 2; i <= numTerms; i++) {
			sb.append(", " + tableArg1 + " alias" + i);
		}
		sb.append(" where ");
		sb.append("	tab.id = alias1.tabid ");
		sb.append(" and tab.factName = 'means' ");
		sb.append(" and alias1.tabid = alias2.tabid ");
		sb.append(" and alias1.term <> alias2.term ");
		for (int i = 3; i <= numTerms; i++) {
			sb.append(" and alias1.tabid = alias" + i + ".tabid ");
			sb.append(" and alias1.term <> alias" + i + ".term ");
		}
		for (int i = 1; i <= numTerms; i++)
			sb.append(" and alias" + i + ".term = ? ");

		return sb.toString();
	}
	
	// query excludes the fact name 'means'
	private String getSelectTableMatchSingleTermArg1OrArg2SQL(String tablename) {
		String tableArg1 = getArgsTableName(tablename, TypeOfMatching.arg1);
		String tableArg2 = getArgsTableName(tablename, TypeOfMatching.arg2);

		StringBuilder sb = new StringBuilder();
		sb.append("(select tab.id, 1 from ");
		sb.append(tablename);
		sb.append(" tab, ");
		sb.append(tableArg1);
		sb.append(" aliasArg1 ");
		sb.append(" where tab.id = aliasArg1.tabid ");
		sb.append(" and tab.isArg1SingleTerm = true ");
		sb.append(" and tab.factName <> 'means' ");
		sb.append(" and aliasArg1.term = ? )");

		sb.append(" union distinct ");

		sb.append("(select tab.id, 2 from ");
		sb.append(tablename);
		sb.append(" tab, ");
		sb.append(tableArg2);
		sb.append(" aliasArg2 ");
		sb.append(" where tab.id = aliasArg2.tabid ");
		sb.append(" and tab.isArg2SingleTerm = true ");
		sb.append(" and tab.factName <> 'means' ");
		sb.append(" and aliasArg2.term = ? )");

		return sb.toString();
	}

	// query excludes the fact name 'means'
	private String getSelectTableMatchMultipleTermArg1OrArg2SQL(
			String tablename, int numTerms) {
		String tableArg1 = getArgsTableName(tablename, TypeOfMatching.arg1);
		String tableArg2 = getArgsTableName(tablename, TypeOfMatching.arg2);

		StringBuilder sb = new StringBuilder();
		sb.append("(select distinct tab.id, 1 from ");
		sb.append(tablename + " tab, ");
		sb.append(tableArg1 + " alias1");
		for (int i = 2; i <= numTerms; i++) {
			sb.append(", " + tableArg1 + " alias" + i);
		}
		sb.append(" where ");
		sb.append("	tab.id = alias1.tabid ");
		sb.append(" and tab.factName <> 'means' ");
		sb.append(" and alias1.tabid = alias2.tabid ");
		sb.append(" and alias1.term <> alias2.term ");
		for (int i = 3; i <= numTerms; i++) {
			sb.append(" and alias1.tabid = alias" + i + ".tabid ");
			sb.append(" and alias1.term <> alias" + i + ".term ");
		}

		for (int i = 1; i <= numTerms; i++)
			sb.append(" and alias" + i + ".term = ? ");

		sb.append(") union distinct ");

		sb.append("(select distinct tab.id, 2 from ");
		sb.append(tablename + " tab, ");
		sb.append(tableArg2 + " aliasB1");
		for (int i = 2; i <= numTerms; i++) {
			sb.append(", " + tableArg2 + " aliasB" + i);
		}
		sb.append(" where ");
		sb.append("	tab.id = aliasB1.tabid ");
		sb.append(" and tab.factName <> 'means' ");
		sb.append(" and	aliasB1.tabid = aliasB2.tabid ");
		sb.append(" and aliasB1.term <> aliasB2.term ");
		for (int i = 3; i <= numTerms; i++) {
			sb.append(" and aliasB1.tabid = aliasB" + i + ".tabid ");
			sb.append(" and aliasB1.term <> aliasB" + i + ".term ");
		}

		for (int i = 1; i <= numTerms; i++)
			sb.append(" and aliasB" + i + ".term = ? ");
		sb.append(")");

		return sb.toString();
	}

	private String getSelectTableMatchSingleTermSQL(String tablename,
			TypeOfMatching whichArg) {
		String table = getArgsTableName(tablename, whichArg);
		StringBuilder sb = new StringBuilder();
		sb.append("select alias1.tabid from ");
		sb.append(table);
		sb.append(" alias1 inner join ");
		sb.append(tablename);
		sb.append(" alias2 ");
		sb.append(" where alias1.tabid = alias2.id ");
		if (whichArg == TypeOfMatching.arg1)
			sb.append(" and alias2.isArg1SingleTerm = true ");
		else
			sb.append(" and alias2.isArg2SingleTerm = true ");

		sb.append(" and alias1.term = ? ");

		return sb.toString();
	}

	// assumes numTerms >= 2
	private String getSelectTableMatchMultipleTermSQL(String tablename,
			TypeOfMatching whichArg, int numTerms) {
		String table = getArgsTableName(tablename, whichArg);

		//
		StringBuilder sb = new StringBuilder();
		sb.append("select distinct alias1.tabid from ");
		sb.append(table + " alias1");
		for (int i = 2; i <= numTerms; i++) {
			sb.append(", " + table + " alias" + i);
		}

		sb.append(" where ");
		sb.append("	alias1.tabid = alias2.tabid ");
		sb.append("and	alias1.term <> alias2.term ");
		for (int i = 3; i <= numTerms; i++) {
			sb.append(" and alias1.tabid = alias" + i + ".tabid ");
			sb.append("	and alias1.term <> alias" + i + ".term ");
		}
		for (int i = 1; i <= numTerms; i++)
			sb.append(" and alias" + i + ".term = ? ");

		return sb.toString();
	}

	private String getInsertResultsSQL(String tablename) {
		return "insert ignore into " + tablename + " (tabid, matchString, whichArg) values (?, ?, ?)";
	}

	// initialize list of PreparedStatement for the multipleterm
	private static final int MAX_TERMS_MULTIPLE_STATEMENT = 10;

	private List<PreparedStatement> getPreparedStatementMatchMultipleTermListArg1GetTerm2(
			String tableName) throws SQLException {
		return getPreparedStatementMatchMultipleTermList(tableName,
				TypeOfMatching.means, null);
	}

	private List<PreparedStatement> getPreparedStatementMatchMultipleTermListArg1AndArg2(
			String tableName) throws SQLException {
		return getPreparedStatementMatchMultipleTermList(tableName,
				TypeOfMatching.arg1and2, null);
	}

	private List<PreparedStatement> getPreparedStatementMatchMultipleTermListArg1OrArg2(
			String tableName) throws SQLException {
		return getPreparedStatementMatchMultipleTermList(tableName,
				TypeOfMatching.arg1or2, null);
	}

	private List<PreparedStatement> getPreparedStatementMatchMultipleTermList(
			String tableName, TypeOfMatching whichArg) throws SQLException {
		return getPreparedStatementMatchMultipleTermList(tableName, whichArg,
				whichArg);
	}

	// whichArg is only needed for
	// whichTable = typeOfMatching.arg1 || typeOfMatching.arg2
	private List<PreparedStatement> getPreparedStatementMatchMultipleTermList(
			String tableName, TypeOfMatching whichTable, TypeOfMatching whichArg)
			throws SQLException {
		List<PreparedStatement> list = new ArrayList<PreparedStatement>(
				MAX_TERMS_MULTIPLE_STATEMENT);
		PreparedStatement stmt;
		for (int i = 0; i <= MAX_TERMS_MULTIPLE_STATEMENT; i++) {
			// first item in the list is for 2 arguments!!
			switch (whichTable) {
			case arg1:
			case arg2:
				stmt = conn
						.prepareStatement(getSelectTableMatchMultipleTermSQL(
								tableName, whichArg, i + 2));
				list.add(stmt);
				break;
			case arg1and2:
				stmt = conn
						.prepareStatement(getSelectTableMatchMultipleTermArg1AndArg2SQL(
								tableName, i + 2));
				list.add(stmt);
				break;
			case arg1or2:
				stmt = conn
						.prepareStatement(getSelectTableMatchMultipleTermArg1OrArg2SQL(
								tableName, i + 2));
				list.add(stmt);
				break;
			case means:
				stmt = conn
						.prepareStatement(getSelectTableMatchMultipleTermArg1GetArg2SQL(
								tableName, i + 2));
				list.add(stmt);

				break;
			}
		}

		return list;
	}

	private PreparedStatement getPreparedStatementForMultipleTerm(
			List<PreparedStatement> list, int numTerms) {
		// because the first item in the list is for 2 arguments!!
		return list.get(numTerms - 2);
	}

	private void commitStatement(PreparedStatement stmt) throws SQLException {
		System.out.println("committed batch of " + this.BATCH_MAX);
		try{
			stmt.executeBatch();
		}catch(BatchUpdateException ex){
			System.out.println(ex.getMessage() + " state " + ex.getSQLState());
		}
		conn.commit();
	}

	// returns the count
	private int addBatchInsertResults(PreparedStatement insertStatement,
			ResultSet matchResult
			, String string2match
			, TypeOfMatching whichTable) throws SQLException {
		int count = 0;
		while (matchResult.next()) {
			insertStatement.setLong(1, matchResult.getLong(1));
			insertStatement.setString(2, string2match);
			switch(whichTable){
			case arg1:
				insertStatement.setInt(3, 1);
				break;
			case arg2:
				insertStatement.setInt(3, 2);
				break;
			case arg1or2:
				insertStatement.setInt(3, matchResult.getInt(2));
				break;
			default:
				insertStatement.setInt(3, 0);	
				break;
					
			}
			insertStatement.addBatch();
			count++;
		}
		return count;
	}

	private static final char SEPARATOR_UNDERSCORE = '_';
	private static final char SEPARATOR_WHITE_SPACE = ' ';

}
